using System;
using System.Data;
using System.Data.SqlClient;
using SalesAndStoreApp.DAL.DAO;

namespace SalesAndStoreApp.DAL.Gateway
{
    public class PurchaseGateway:DbGateway 
    {
        private InventoryGateway inventoryGateway = new InventoryGateway() ;

        public bool Save(ProductCarriage productCarriage)
        {
            Connection.Open();
            using (var transaction = Connection.BeginTransaction())
            {
                Command.Transaction = transaction;
                try
                {
                    InsertInPurchase(productCarriage);
                    productCarriage.Id = GetLastInsertedId();
                    InsertInProductsInPurchaseDetails(productCarriage);
                    inventoryGateway.InsertOrUpdate(productCarriage.Products);
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                    throw new Exception("Save couldn't performed due to system error!",exception);
                }
            }
        }

       

        private void InsertInProductsInPurchaseDetails(ProductCarriage productCarriage)
        {
            try
            {
                string query = "INSERT INTO PurchaseDetails VALUES(@productId,@inventoryId,@qty)";
                Command.CommandText = query;
                foreach (var product in productCarriage.Products)
                {
                    Command.Parameters.Clear();
                    Command.Parameters.Add("@inventoryId", SqlDbType.Int);
                    Command.Parameters["@inventoryId"].Value = productCarriage.Id;
                    Command.Parameters.Add("@productId", SqlDbType.Int);
                    Command.Parameters["@productId"].Value = product.Id;
                    Command.Parameters.Add("@qty", SqlDbType.Int);
                    Command.Parameters["@qty"].Value = product.Inventory.Quantity;
                    Command.ExecuteNonQuery();
                }

            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred while trying to insert purchase information",exception);
            }
            
        }

        private int GetLastInsertedId()
        {
            const string query = "SELECT * FROM Purchase ORDER BY Id DESC";
            Command.CommandText = query;
            int lastId = 0;
            using (SqlDataReader dataReader = Command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    lastId = int.Parse(dataReader["Id"].ToString());
                    break;
                }
            }
            if (lastId == 0)
            {
                throw new Exception("There is no Order Inserted while trying to retrieve a Order");
            }
            return lastId;
        }

        private void InsertInPurchase(ProductCarriage productCarriage)
        {
            try
            {
                const string query = "INSERT INTO Purchase VALUES(@vendorId,@storeDate,@totalPrice)";
                Command.CommandText = query;
                Command.Parameters.Add("@vendorId", SqlDbType.Int);
                Command.Parameters["@vendorId"].Value = productCarriage.Vendor.Id;
                Command.Parameters.Add("@storeDate", SqlDbType.Date);
                Command.Parameters["@storeDate"].Value = productCarriage.Date;
                Command.Parameters.Add("@totalPrice", SqlDbType.Real);
                Command.Parameters["@totalPrice"].Value = productCarriage.TotalPrice;
                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("Error Occurred while trying to insert purchase information",exception);
            }
            
        }
    }
}